import os
import sqlite3


def create_db():
    file_path = './ip_adress.sqlite3'
    if os.path.isfile(file_path):
        print('已经存在sqlite3')
        return
    conn = sqlite3.connect('./ip_adress.sqlite3')
    print("Opened database successfully")
    c = conn.cursor()
    c.execute('''CREATE TABLE ip_template
           (ID INT PRIMARY KEY NOT NULL,
           start_ip  CHAR(50) NOT NULL,
           end_ip CHAR(50) NOT NULL,
           country CHAR(50),
           sheng CHAR(50),
           city CHAR(50),
           desc  CHAR(50));''')
    print("Table created successfully")
    conn.commit()
    conn.close()


def import_ip():
    file_path = '../../data/ip.merge.txt'
    if os.path.isfile(file_path):
        with open(file_path, 'rb') as fp:
            INDEX = 0
            for items in fp.readlines():
                conn = sqlite3.connect('./ip_adress.sqlite3')
                c = conn.cursor()
                item = items.decode('utf-8').split('|')
                sql = """insert into ip_template values ({}, "{}", "{}", "{}", "{}", "{}", "{}")""".format(INDEX + 1,
                                                                                                      item[0],
                                                                                                      item[1],
                                                                                                      item[2],
                                                                                                      item[4],
                                                                                                      item[5],
                                                                                                      item[6])
                c.execute(sql)
                conn.commit()
                print('进度:%d' % INDEX)
                INDEX += 1
    else:
        print('路径不存在')


if __name__ == '__main__':
    create_db()
    import_ip()
